const db = require('@/db/db-connection')
const { multipleColumnSet, newRandomId, dateFormat } = require('@/utils/common.util')

class UserModel {
  constructor () {
    this.tableName = 'user'
  }

  /**
   * search users
   * @param {*} param
   * @returns
   */
  async find (param) {
    try {
      const { pageNo = 1, pageSize = 10, ...object } = param

      const { columnSet, values } = multipleColumnSet(object)

      let sql = `SELECT * FROM ${this.tableName}`

      if (!columnSet) {
        const totalSql = `SELECT COUNT(*) as total FROM ${this.tableName}`

        const list = await db.query(sql)

        const total = await db.query(totalSql)

        sql += ` LIMIT ${(pageNo - 1) * pageSize}, ${pageSize}`

        return {
          list,
          total: total[0].total,
          pageNo
        }
      }

      sql += ` WHERE ${columnSet} LIMIT ${(pageNo - 1) * pageSize}, ${pageSize}`

      const totalSql = `SELECT COUNT(*) as total FROM ${this.tableName}  WHERE ${columnSet}`

      const list = await db.query(sql, values)

      const total = await db.query(totalSql, values)
      return {
        list,
        total: total[0].total,
        pageNo
      }
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * search userInfo by id or username
   * @param {*} param
   * @param {*} hidepassword
   * @returns
   */
  async findOne (param, hidepassword) {
    try {
      const { columnSet, values } = multipleColumnSet(param, ' AND ')

      const sql = `SELECT * FROM ${this.tableName} WHERE ${columnSet}`

      const result = (await db.query(sql, values))[0]

      if (!result) {
        return null
      }

      return {
        ...result,
        password: hidepassword ? undefined : result.password,
        sessionKey: undefined
      }
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * delete user by id
   * @param {*} id
   */
  async delete (id) {
    try {
      const sql = `DELETE FROM ${this.tableName} WHERE id = ?`

      await db.query(sql, [id])
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * create user
   * @param {*} param
   */
  async create ({ username, password, email, openid, sessionKey, nickname, avatar, giteeId, giteeName, githubId, githubName }) {
    try {
      const id = newRandomId()

      const createTime = dateFormat(new Date())

      const bindPassword = Number(!!password)

      const randomNickname = `用户${String(Math.random()).substring(2, 15)}`

      const sql = `INSERT INTO ${this.tableName} 
      
      (id, username, nickname, password, status, email, bindPassword, giteeId, giteeName, githubId, githubName, openid, sessionKey, avatar, createTime) 
      
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`

      await db.query(sql, [id, username, nickname || randomNickname, password, '1', email, bindPassword, giteeId, giteeName, githubId, githubName, openid, sessionKey, avatar, createTime])

      return id
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * update user
   * @param {*} param0
   * @param {*}
   */
  async update ({ id, username, password, ...object }) {
    const { columnSet, values } = multipleColumnSet({
      ...object,
      updateTime: dateFormat(new Date())
    })
    try {
      const sql = `UPDATE ${this.tableName} SET ${columnSet} WHERE id = ?`

      await db.query(sql, [...values, id])
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * remove openid or email
   * @param {*} id 用户id
   * @param {*} type 解绑类型
   */
  async remove (id, type) {
    try {
      let sql = `UPDATE ${this.tableName} SET`

      if (type === 'weixin') {
        sql += ' openid = NULL, sessionKey = NULL'
      } else if (type === 'email') {
        sql += ' email = NULL'
      } else if (type === 'gitee') {
        sql += ' giteeId = NULL, giteeName = NULL'
      } else if (type === 'github') {
        sql += ' githubId = NULL, githubName = NULL'
      }

      sql += ' WHERE id = ?'
      await db.query(sql, [id])
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * user bind username
   * @param {*} param0
   */
  async bindUserName ({ id, username, password }) {
    const updateTime = dateFormat(new Date())
    try {
      const sql = `UPDATE ${this.tableName} SET username = ?, password = ?, bindPassword = 1, updateTime = ? WHERE id = ?`

      await db.query(sql, [username, password, updateTime, id])
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * update user password
   * @param {*} password
   * @param {*} id
   */
  async updatePassword (password, id) {
    try {
      const sql = `UPDATE ${this.tableName} SET password = ?, bindPassword = 1 WHERE id = ?`

      await db.query(sql, [password, id])
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * find user rank by article counts last week
   * @param {*} pageNo
   * @param {*} pageSize
   * @param {*} sessionId
   * @returns
   */
  async findUserArticleRank (pageNo, pageSize, sessionId) {
    try {
      const sql = `SELECT t.id, t.nickname, t.avatar, t.job, t.exp,

        (SELECT SUM(a.viewCounts) FROM article AS a WHERE t.id = a.userId GROUP BY a.userId) AS got_view_counts,

        (SELECT COUNT(*) FROM user_agree_article AS uaa WHERE uaa.articleId in 
          
          (SELECT id FROM article AS a WHERE a.userId = t.id)) AS got_agree_counts,

        (SELECT focusId FROM user_focus_user AS ufu WHERE ufu.focusId = t.id AND ufu.userId = ?) AS hasFocused
      
        FROM ${this.tableName} AS t
        
        ORDER BY (SELECT COUNT(*) FROM article AS a WHERE a.userId = t.id AND a.createTime > DATE_SUB(NOW(), INTERVAL 1 WEEK)) DESC 
        
        LIMIT ${pageSize * (pageNo - 1)}, ${pageSize}`

      const list = await db.query(sql, [sessionId])

      return list.map(o => {
        return {
          ...o,
          hasFocused: Boolean(o.hasFocused)
        }
      })
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * search user by kwywords
   * @param {*} pageNo
   * @param {*} pageSize
   * @param {*} value
   */
  async vagueFind ({ pageNo, pageSize, value }, sessionId) {
    try {
      const sql = `SELECT id, avatar, nickname, job,

        (SELECT COUNT(*) FROM article AS a WHERE a.userId = t.id) AS articleCount,

        (SELECT COUNT(*) FROM user_focus_user AS ufu WHERE ufu.focusId = t.id) AS focusedCount,

        (SELECT COUNT(*) FROM share_circle AS sc WHERE sc.userId = t.id) AS circleCount,

        (SELECT userId FROM user_focus_user AS ufu WHERE ufu.focusId = t.id AND ufu.userId = ?) AS isFocuser
      
        FROM ${this.tableName}
        
        AS t WHERE t.nickname LIKE ?
        
        LIMIT ${(pageNo - 1) * pageSize}, ${pageSize}`

      const totalSql = `SELECT COUNT(*) AS total FROM ${this.tableName} AS t WHERE t.nickname LIKE ?`

      value = value.replace(/%/g, '\\%').replace(/_/g, '\\_')

      const [list, total] = [await db.query(sql, [sessionId, `%${value}%`]), await db.query(totalSql, [`%${value}%`])]

      return {
        list: list.map(u => {
          return {
            ...u,
            isFocuser: Boolean(u.isFocuser)
          }
        }),

        total: total[0].total,

        pageNo: Number(pageNo)
      }
    } catch (error) {
      throw new Error(error)
    }
  }

  async updateExp (userId, exp) {
    const sql = `UPDATE user SET exp = exp + ${exp} WHERE id = '${userId}'`

    try {
      await db.query(sql)
    } catch (error) {
      throw new Error(error)
    }
  }
}

module.exports = new UserModel()
